﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MediaLibraryManagement.Entity;

namespace MediaLibraryManagement.DAL
{
    public class UserDAL : BaseDAL
    {
        #region Properties Implementation

        protected override string TableName { get { return DatabaseTableName.Users; } }

        #endregion

        #region Public Methods

        public User GetUserByUserName(string userName)
        {
            User user = new User();
            try
            {
                OpenConnection();
                string commandText = string.Format("SELECT TOP 1 * FROM {0} WHERE LOWER(UserName) = '{1}'", TableName, userName.ToLowerInvariant());
                using (SqlCommand command = new SqlCommand(commandText, Connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            user = (User)Fetch(reader);
                        }

                        reader.Close();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }

            return user;
        }

        public void Insert(User user)
        {
            try
            {
                OpenConnection();
                string commandstring = string.Format("INSERT INTO {0}(UserName, Password, FirstName, LastName, DateOfBirth, Gender, Address, Email, SecurityQuestion, SecurityAnswer, Role, JoinedDate, IsActive) " +
                                                             "VALUES (@UserName, @Password, @FirstName, @LastName, @DateOfBirth, @Gender, @Address, @Email, @SecurityQuestion, @SecurityAnswer, @Role, @JoinedDate, @IsActive)", TableName);
                using (SqlCommand command = new SqlCommand(commandstring, Connection))
                {
                    command.Parameters.AddWithValue("@UserName", user.UserName);
                    command.Parameters.AddWithValue("@Password", user.Password);
                    command.Parameters.AddWithValue("@FirstName", user.FirstName);
                    command.Parameters.AddWithValue("@LastName", user.LastName);
                    command.Parameters.AddWithValue("@DateOfBirth", user.DateOfBirth);
                    command.Parameters.AddWithValue("@Gender", user.Gender.ToString());
                    command.Parameters.AddWithValue("@Address", user.Address);
                    command.Parameters.AddWithValue("@Email", user.Email);
                    command.Parameters.AddWithValue("@SecurityQuestion", user.SecurityQuestion);
                    command.Parameters.AddWithValue("@SecurityAnswer", user.SecurityAnswer);
                    command.Parameters.AddWithValue("@Role", user.Privilege.ToString());
                    command.Parameters.AddWithValue("@JoinedDate", user.JoinedDate);
                    command.Parameters.AddWithValue("@IsActive", user.IsActive);
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        public void Update(User user)
        {
            try
            {
                OpenConnection();
                string commandString = string.Format("UPDATE {0} SET "
                                                        + "FirstName = @FirstName, LastName = @LastName, DateOfBirth = @DateOfBirth, Gender = @Gender, Address = @Address, Email = @Email, SecurityQuestion = @SecurityQuestion, SecurityAnswer = @SecurityAnswer, Role = @Role, JoinedDate = @JoinedDate, IsActive = @IsActive "
                                                        + "WHERE ID = {1}"
                                                    , TableName
                                                    , user.ID);
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    command.Parameters.AddWithValue("@UserName", user.UserName);
                    command.Parameters.AddWithValue("@Password", user.Password);
                    command.Parameters.AddWithValue("@FirstName", user.FirstName);
                    command.Parameters.AddWithValue("@LastName", user.LastName);
                    command.Parameters.AddWithValue("@DateOfBirth", user.DateOfBirth);
                    command.Parameters.AddWithValue("@Gender", user.Gender.ToString());
                    command.Parameters.AddWithValue("@Address", user.Address);
                    command.Parameters.AddWithValue("@Email", user.Email);
                    command.Parameters.AddWithValue("@SecurityQuestion", user.SecurityQuestion);
                    command.Parameters.AddWithValue("@SecurityAnswer", user.SecurityAnswer);
                    command.Parameters.AddWithValue("@Role", user.Privilege.ToString());
                    command.Parameters.AddWithValue("@JoinedDate", user.JoinedDate);
                    command.Parameters.AddWithValue("@IsActive", user.IsActive);
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        public List<User> Search(string username)
        {
            List<User> users = new List<User>();
            try
            {
                OpenConnection();
                string commandString = string.Format("SELECT * FROM {0} WHERE LOWER(UserName) LIKE '%{1}%'", TableName, username);
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            User user = (User)Fetch(reader);
                            users.Add(user);
                        }
                        reader.Close();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
            return users;
        }

        #endregion

        #region Private Methods

        protected override IEntity Fetch(IDataReader reader)
        {
            User user = new User();
            user.ID = (Int32)reader["ID"];
            user.UserName = reader["UserName"].ToString();
            user.Password = reader["Password"].ToString();
            user.FirstName = reader["FirstName"].ToString();
            user.LastName = reader["LastName"].ToString();
            user.DateOfBirth = reader["DateOfBirth"] != DBNull.Value ? (DateTime?)reader["DateOfBirth"] : null;
            user.Gender = reader["Gender"] != DBNull.Value ? (Gender)Enum.Parse(typeof(Gender), reader["Gender"].ToString()) : Gender.Unspecified;
            user.Address = reader["Address"].ToString();
            user.Email = reader["Email"].ToString();
            user.SecurityQuestion = reader["SecurityQuestion"].ToString();
            user.SecurityAnswer = reader["SecurityAnswer"].ToString();
            user.Privilege = (Privileges)Enum.Parse(typeof(Privileges), reader["Role"].ToString());
            user.JoinedDate = (DateTime)reader["JoinedDate"];
            user.IsActive = (bool)reader["IsActive"];
            return user;
        }

        #endregion
    }
}